<?php
/**
 * Created by PhpStorm.
 * User: idcu
 * Date: 2016/3/24
 * Time: 17:01
 */
namespace Admin\Controller;
use Think\Controller;
use IDCU\Office\ExcelOffice;

class DataImportController extends Controller
{
    protected function getReq(){
        $get = I('get.');
        $post = I('post.');
        $req = array_merge($get,$post);
        return $req;
    }
    //户主null数据获取为产权人
    public function dataGet() {
        echo '<pre>';
        $data = M('Room')->select();
        foreach ($data as $k => $v) {
            if($v['householder_name']==NULL || $v['householder_mobi']==NULL){
            $ww['householder_name'] = $v['owner_name'];
            $ww['householder_mobi'] = $v['owner_mobi'];
            M('Room')->data($ww)->where(array('id'=>$v['id']))->save();
            var_dump($v);
            }
        };
    }
    //删除小区数据
    public function deleteData() {
        $community_id = I('community_id');
        $data = M('Family')->where(array('community_id'=>$community_id))->select();
        foreach ($data as $k => $v) {
            $dd = M('FamilyMember')->where(array('family_id'=>$v['id']))->select();
            foreach ($dd as $v1) {
                M('Customer')->where(array('id'=>$v1['customer_id']))->delete();
            }
            M('FamilyMember')->where(array('family_id'=>$v['id']))->delete();
        }
        M('Community')->data(array('children_count'=>0))->where(array('id'=>$community_id))->save();
        M('Zone')->where(array('community_id'=>$community_id))->delete();
        M('Building')->where(array('community_id'=>$community_id))->delete();
        M('Unit')->where(array('community_id'=>$community_id))->delete();
        M('Room')->where(array('community_id'=>$community_id))->delete();
        M('Family')->where(array('community_id'=>$community_id))->delete();
    }
    //自定义删除
    public function delData() {
        $data = M('Room')->where('id > 18394 and id < 19382')->delete();
        die;
        $building['building_id'] = array('in','42,43,44,45,46,47,48,49,50,51,52,53');
        $building_id['id'] = array('in','42,43,44,45,46,47,48,49,50,51,52,53');
        $data = M('Family')->where($building)->select();
        foreach ($data as $k => $v) {
            $dd = M('FamilyMember')->where(array('family_id'=>$v['id']))->select();
            foreach ($dd as $v1) {
                M('Customer')->where(array('id'=>$v1['customer_id']))->delete();
            }
            M('FamilyMember')->where(array('family_id'=>$v['id']))->delete();
        }
        M('Building')->where($building_id)->delete();
        M('Unit')->where($building)->delete();
        M('Room')->where($building)->delete();
        M('Family')->where($building)->delete();
    }
    //自定义转移
    public function setData() {
        $where['id'] = array('between','16741,16961');
//        $where['id'] = array('eq','15666');
        $allData = M('FamilyMember','zh_','DB_DA')->where($where)->select();
        var_dump($allData);
        foreach ($allData as $k=>$v){
            $id=M('FamilyMember')->data($v)->add();
            echo $id;
            echo '<br/>';
        }

    }
    public function houseImport()
    {
        $req = $this->getReq();
        $path = $req['path'];
        $sheetNo = $req['sheetNo'] ? $req['sheetNo'] : 0;
        $startRow = $req['startRow'] ? $req['startRow'] : 1;
        $excelOffice = new ExcelOffice();
        $arr = $excelOffice->getArr($path,$sheetNo,$startRow);

        //从老数据库来的数据
//        $arr4 =M('Room1')->alias('a')->join("left join zh_community1 e on a.community_id = e.id
//        left join zh_zone1 b on e.id=b.community_id
//        left join zh_building1 c on e.id=c.community_id
//        left join zh_unit1 d on e.id=d.community_id
//        ")->select();
//        $arr1 = M('Zone1')->select();
//        $arr2 = M('Building1')->select();
//        $arr3 = M('Unit1')->select();
        //临时数据
//        if (!$arr = S('arr_linshi')) {
//            $arr4 = M('Room1')->select();
//            $arr = array();
//            foreach ($arr4 as $k => $v) {
//                $uu = M('Unit1')->find($v['unit_id']);
//                $bb = M('Building1')->find($v['building_id']);
//                $zz = M('Zone1')->find($v['zone_id']);
//                $arr[$k]['community_id'] = 36;
//                $arr[$k]['room_name'] = $v['number'];
//                $arr[$k]['floor'] = $v['floor'];
//                $arr[$k]['unit_name'] = $uu['alias'] ? $uu['alias'] : 1;
//                $arr[$k]['building_name'] = $bb['alias'];
//                $arr[$k]['zone_name'] = $zz['alias'];
//            }
//            S('arr_linshi', $arr);
//        }
        //数据处理
//        foreach ($arr as $k=>&$v){
//            if($pos = strpos($v['householder_name'],',')){
//                $dd = $v['householder_name'];
//                $v['householder_name'] = explode(',',$v['householder_name'])[0];
//                $v['member'] = $v['member'] ? $v['member'].','.substr($dd,$pos+1) : substr($dd,$pos+1);
//            }else{
//                $v['householder_name'] = $v['householder_name'] ? $v['householder_name'] : null;
//            }
//            $v['building_name'] = explode('-', $v['room_name'])[0];
//        }


//         foreach($arr as $k=>$v){
//             if(strpos($v['room_name'],',')){
//                 $room_name = $v['room_name'];
//                 $v['room_name'] = explode(',',$room_name)[0];
//                 $arr0[] = $v;
//                 $v['room_name'] = explode(',',$room_name)[1];
//                 $arr0[] = $v;
//             }else{
//                 $arr0[] = $v;
//             }
//         }

        myDump($arr);die;

        foreach($arr as $k=>$v){
            $community = array();
            $zone = array();
            $building = array();
            $unit = array();
            $room = array();

            /**
             * 读取该条community数据并关联的子级zones
             */
            $community['id'] = $v['community_id'];
//            var_dump($community['id']);die;
            $community = D('Community')->relation('zones')->find($community['id']);
            /**
             * 判断该条数据中的zone是否已经存在在zone数据表中
             * 如果不存在就插入到zone数据表中，并父级community表中对应的数据children_count字段增1
             * 读取该条zone数据并关联的子级buildings
             */
            if(is_int(array_search($v['zone_name'],array_column($community['zones'],'name')))){
                $zone = D('Zone')->where(array('community_id'=>$community['id'],'name'=>$v['zone_name']))->relation('buildings')->find();
            }else{
                $zone['no'] = $community['no'].sprintf('%02d',($community['children_count']+1));
                $zone['name'] = $v['zone_name'];
//                $zone['ename'] = '';
                $zone['status'] = 1;
                $zone['remark'] = '';
                $zone['community_id'] = $community['id'];
                $zone['full_parent_name'] = $community['full_parent_name'].$community['name'];
                $zone['children_count'] = 0;

                $zone['id'] = D('Zone')->add($zone);
                D('Community')->where(array('id'=>$community['id']))->setInc('children_count',1);
                if(!$zone['id'])
                $zone = D('Zone')->relation('buildings')->find($zone['id']);

                echo '<pre>';
                var_dump($zone);
                echo '<br/>';
            }
            /**
             * 判断该条数据中的building是否已经存在在building数据表中
             * 如果不存在就插入到building数据表中，并父级zone表中对应的数据children_count字段增1
             * 读取该条building数据并关联的子级units
             */
            if(is_int(array_search($v['building_name'],array_column($zone['buildings'],'name')))){
                $building = D('Building')->where(array('zone_id'=>$zone['id'],'name'=>$v['building_name']))->relation('units')->find();
            }else{
                $building['no'] = $zone['no'].sprintf('%03d',($zone['children_count']+1));
                $building['name'] = $v['building_name'];
//                $building['ename'] = '';
                $building['status'] = 1;
//                $building['remark'] = '';
                $building['community_id'] = $community['id'];
                $building['zone_id'] = $zone['id'];
                $building['full_parent_name'] = $zone['full_parent_name'];
//                $building['full_parent_name'] = $zone['full_parent_name'].$zone['name'];
                $building['children_count'] = 0;
               
                $building['id'] = D('Building')->add($building);
                echo '<pre>';
                var_dump($building);
                echo '<br/>';
                D('Zone')->where(array('id'=>$zone['id']))->setInc('children_count',1);

                $building = D('Building')->relation('units')->find($building['id']);
            }
            /**
             * 判断该条数据中的unit是否已经存在在unit数据表中
             * 如果不存在就插入到unit数据表中，并父级building表中对应的数据children_count字段增1
             * 读取该条unit数据并关联的子级rooms
             */
            if(is_int(array_search($v['unit_name'],array_column($building['units'],'name')))){
                $unit = D('Unit')->where(array('building_id'=>$building['id'],'name'=>$v['unit_name']))->relation('rooms')->find();
            }else{
                $unit['no'] = $building['no'].sprintf('%01d',($building['children_count']+1));
                $unit['name'] = $v['unit_name'];
//                $unit['ename'] = '';
                $unit['status'] = 1;
                $unit['remark'] = '';
                $unit['community_id'] = $community['id'];
                $unit['zone_id'] = $zone['id'];
                $unit['building_id'] = $building['id'];
                $unit['full_parent_name'] = $building['full_parent_name'].$building['name'];
                $unit['children_count'] = 0;


                $unit['id'] = D('Unit')->add($unit);
                D('Building')->where(array('id'=>$building['id']))->setInc('children_count',1);

                echo '<pre>';
                var_dump($unit);
                echo '<br/>';

                $unit = D('Unit')->relation('rooms')->find($unit['id']);
            }
            /**
             * 判断该条数据中的room是否已经存在在room数据表中
             * 如果不存在就插入到room数据表中，并unit表中对应的数据children_count字段增1
             * 读取该条room数据并关联的子级families
             */
            if(is_int(array_search($v['room_name'],array_column($building['rooms'],'name')))){
                $room = D('Room')->where(array('unit_id'=>$unit['id'],'name'=>$v['unit_name']))->find();
            }else{
                $room['no'] = $unit['no'].sprintf('%04d',($unit['children_count']+1));
                $room['name'] = $v['add'].$v['room_name'];
//                $room['ename'] = '';
                $room['status'] = 1;
                $room['remark'] = '';
                $room['community_id'] = $community['id'];
                $room['zone_id'] = $zone['id'];
                $room['building_id'] = $building['id'];
                $room['unit_id'] = $unit['id'];
                $room['full_parent_name'] = $unit['full_parent_name'].$unit['name'];
                $room['children_count'] = 0;
                $room['type'] = $v['type'] ? $v['type'] : null;
                $room['area'] = $v['area'] ? $v['area'] : null;
                $room['basement'] = $v['basement'] ? $v['basement'] : null;
                $room['attic'] = $v['attic'] ? $v['attic'] : null;
                $room['garden'] = $v['garden'] ? $v['garden'] : null;
                $room['toward'] = $v['toward'] ? $v['toward'] : null;
                $room['owner_id_no'] = $v['owner_id_no'] ? $v['owner_id_no'] : null;
                $room['owner_name'] = $v['owner_name'] ? $v['owner_name'] : null;
                $room['owner_contact'] = $v['owner_contact'] ? $v['owner_contact'] : null;
                if($pos = strpos($v['owner_mobi'],',')){
                    $room['owner_mobi'] = explode(',',$v['owner_mobi'])[0];
                    $room['owner_contact'] = $room['owner_contact'] ? $room['owner_contact'].','.substr($v['owner_mobi'],$pos+1) : substr($v['owner_mobi'],$pos+1);
                }else{
                    $room['owner_mobi'] = $v['owner_mobi'] ? $v['owner_mobi'] : null;
                }
                $room['owner_phone'] = $v['owner_phone'] ? $v['owner_phone'] : null;
                $room['householder_id_no'] = $v['householder_id_no'] ? $v['householder_id_no'] : null;
                $room['householder_name'] = $v['householder_name'] ? $v['householder_name'] : null;
                $room['householder_contact'] = $v['householder_contact'] ? $v['householder_contact'] : null;
                if($pos = strpos($v['householder_mobi'],',')){
                    $room['householder_mobi'] = explode(',',$v['householder_mobi'])[0];
                    $room['householder_contact'] = $room['householder_contact'] ? $room['householder_contact'].','.substr($v['householder_mobi'],$pos+1) : substr($v['householder_mobi'],$pos+1);
                }else{
                    $room['householder_mobi'] = $v['householder_mobi'] ? $v['householder_mobi'] : null;
                }
                $room['householder_phone'] = $v['householder_phone'] ? $v['householder_phone'] : null;
//                $room['other_household'] = $v['other_household'] ? $v['other_household'] : null;
                $room['householder_old_address'] = $v['householder_old_address'] ? $v['householder_old_address'] : null;
                $room['vehicle'] = $v['vehicle'] ? $v['vehicle'] : null;
                $room['parking'] = $v['parking'] ? $v['parking'] : null;

                $roomModel = D('Room');
                $room = $roomModel->create($room);
                $room['id']= $roomModel->add($room);

                echo '<pre>';
                var_dump($room);
                echo '<br/>';
                D('Unit')->where(array('id'=>$unit['id']))->setInc('children_count',1);

                $room = D('Room')->relation('families')->find($room['id']);
            }
        }
    }

    public function customerImport(){
        set_time_limit(0);
//        $count = D('Room')->max('id');
//        echo $count;die;
//        for($i=1;$i<=$count/100+1;$i++) {
        $i = I('param.i');
//            $rooms = D('Room')->page($i, 1000)->select();
        $rooms = D('Room')->page($i, 1000)->order('id desc')->select();
//            $rooms = D('Room')->select();
//            echo '<pre>';
            echo $i.'<br/>';
//        echo '<pre>';
//            var_dump($rooms);die;
            foreach ($rooms as $k => $v) {
                echo $v['householder_name'];
                echo '<br/>';
                if($v['householder_mobi']){
                    $where['tel'] = $v['householder_mobi'];
                }
                elseif ($v['owner_mobi']){
                    $where['tel'] = $v['owner_mobi'];
                }
                if($v['householder_name']){
                    $where['name'] = $v['householder_name'];
                }
                elseif($v['owner_name']){
                    $where['name'] = $v['owner_name'];
                }
//                echo '<pre>';
//                var_dump($where);
                if($v['householder_name']==null && $v['householder_mobi']==null){
                    if($v['owner_name']==null && $v['owner_mobi']==null)
                        echo '！数据空退出';
                        echo '<br/>';
                        continue;
                }
                if ($ddd = D('Customer')->where($where)->find()) {
                    $customer_id = $ddd['id'];
                    if($find = D('Family')->where(array('room_id'=>$v['id']))->find()){
                        echo '发现家庭'.$find['id'];
                        echo '发现用户'.$ddd['id'];
                        echo '<pre>';
                    }else{
                        $family['no'] = $v['no'] . sprintf('%02d', ($v['children_count'] + 1));
                        $family['community_id'] = $v['community_id'];
                        $family['zone_id'] = $v['zone_id'];
                        $family['building_id'] = $v['building_id'];
                        $family['unit_id'] = $v['unit_id'];
                        $family['room_id'] = $v['id'];
                        $family['full_parent_name'] = $v['full_parent_name'].$v['name'];
                        $family['address'] = $family['full_parent_name'];
                        $family['customer_id'] = $customer_id;
                        $family['checkin_date'] = null;
                        $family['status'] = 1;
                        $family['create_time'] = time();
                        $family['modify_time'] = time();
                        $family['delete_time'] = null;
    //                    dump($family);
                        $family_id = D('Family')->add($family);
    //                    dump(D('Family')->getDbError());

                        $family_member['family_id'] = $family_id;
                        $family_member['customer_id'] = $customer_id;
                        $family_member['prefix'] = '户主';
                        $family_member['title'] = null;
                        $family_member['role'] = null;
                        $family_member['name'] = $ddd['name'];
                        $family_member['create_time'] = time();
                        $family_member['modify_time'] = time();

                        $familymId = D('FamilyMember')->add($family_member);
    //                    dump(D('FamilyMember')->getDbError());exit;
                        echo '添加家庭和成员';
                        echo $customer_id.$family_id.$familymId;
                        echo '<br/>';
                    }
                    //判断用户是否在家庭中
                    $ismenmber = D('FamilyMember')->where(array('room_id'=>$v['id'], 'name'=>$ddd['name']))->find();
                    if(!$ismenmber){
                        $family_member['family_id'] = $family_id;
                        $family_member['customer_id'] = $customer_id;
                        $family_member['prefix'] = '户主';
                        $family_member['title'] = null;
                        $family_member['role'] = null;
                        $family_member['name'] = $ddd['name'];
                        $family_member['create_time'] = time();
                        $family_member['modify_time'] = time();
                        $familymId = D('FamilyMember')->add($family_member);
                        echo'存在用户添加进家庭';
                        echo $customer_id.$family_id.$familymId;
                        echo '<br/>';
                    }

                } else {
                        if($v['householder_name']==null && $v['householder_mobi']==null){
                            if($v['owner_name']==null && $v['owner_mobi']==null)
                            continue;
                        }
                        $customer['customer_no'] = date('YmdHis', time()) . rand(1000, 9999);
                        $customer['id_no'] = $v['householder_id_no']!=null ? $v['householder_id_no'] : null;
                        $customer['name'] = $where['name'];
                        $customer['tel'] = $where['tel'];
                        $customer['phone'] = $v['householder_phone'];
                        $customer['contact'] = $v['householder_contact'];
                        $customer['sex'] = 'M';
                        $customer['birthday'] = $v['householder_id_no'] ? getBirthday($v['householder_id_no']) : null;
                        $customer['ugroup'] = 1;
                        $customer['status'] = 1;
                        $customer['create_time'] = time();
                        $customer['modify_time'] = time();
                        $customer['creator_id'] = 5;
                        $customer['modifier_id'] = 5;
                        $customer['deleted'] = 0;
                        $customer['hot'] = 0;
                        $customer['avatar'] = 0;
    //                    $this->ajaxReturn($customer);

                        $customer_id = D('Customer')->add($customer);
    //                    dump(D('Customer')->getDbError());exit;

                        $family['no'] = $v['no'] . sprintf('%02d', ($v['children_count'] + 1));
                        $family['community_id'] = $v['community_id'];
                        $family['zone_id'] = $v['zone_id'];
                        $family['building_id'] = $v['building_id'];
                        $family['unit_id'] = $v['unit_id'];
                        $family['room_id'] = $v['id'];
                        $family['full_parent_name'] = $v['full_parent_name'].$v['name'];
                        $family['address'] = $family['full_parent_name'];
                        $family['customer_id'] = $customer_id;
                        $family['checkin_date'] = null;
                        $family['status'] = 1;
                        $family['create_time'] = time();
                        $family['modify_time'] = time();
                        $family['delete_time'] = null;
    //                    dump($family);
                        $family_id = D('Family')->add($family);
    //                    dump(D('Family')->getDbError());

                        $family_member['family_id'] = $family_id;
                        $family_member['customer_id'] = $customer_id;
                        $family_member['prefix'] = '户主';
                        $family_member['title'] = null;
                        $family_member['role'] = null;
                        $family_member['name'] = $customer['name'];
                        $family_member['create_time'] = time();
                        $family_member['modify_time'] = time();

                        $familymId = D('FamilyMember')->add($family_member);
    //                    dump(D('FamilyMember')->getDbError());exit;
                        echo '全部添加';
                        echo $customer_id.$family_id.$familymId;
                        echo '<br/>';
    //                }
                }
            }
    }
}